import os
import json
from flask import Flask, render_template, send_file, request, flash
from werkzeug.utils import secure_filename
from flask_wtf import FlaskForm
from flask_wtf.file import FileField, FileAllowed
import pandas as pd
import numpy as np

app = Flask(__name__)
app.config['SECRET_KEY'] = os.urandom(24)
app.config['UPLOAD_FOLDER'] = 'uploads'
app.config['MAX_CONTENT_LENGTH'] = 100 * 1024 * 1024  # 100MB

class UploadForm(FlaskForm):
    file = FileField('Excel文件', validators=[
        FileAllowed(['xls', 'xlsx', 'csv'], '仅支持Excel和CSV文件')
    ])

def lixi(x):
    jisuan = lambda xxx: xxx*0.03/365 if xxx > 0 else xxx*0.04/365
    return x.apply(jisuan)

@app.route('/', methods=['GET', 'POST'])
def upload():
    form = UploadForm()
    if form.validate_on_submit():
        try:
            # 处理文件上传和验证
            f = form.file.data
            
            # 检查文件是否存在
            if not f or not hasattr(f, 'filename') or f.filename.strip() == '':
                flash('请选择要上传的文件', 'danger')
                return render_template('upload.html', form=form)
                
            filename = secure_filename(f.filename)
            if not filename.lower().endswith(('.xls', '.xlsx', '.csv')):
                flash('仅支持Excel和CSV文件', 'danger')
                return render_template('upload.html', form=form)

            # 确保上传目录存在
            os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True)
            
            filepath = os.path.join(app.config['UPLOAD_FOLDER'], filename)
            f.save(filepath)
            
            # 处理Excel文件
            df = pd.read_excel(filepath, sheet_name='Sheet1', engine='openpyxl')

            # 数据预处理
            # 转换日期字段为datetime类型
            df['过账日期'] = pd.to_datetime(df['过账日期'], errors='coerce')
            df = df.dropna(subset=['过账日期'])
            
            dfs = df[df['科目描述'].isin(['银行存款.综合户.中信银行.深圳皇岗支行6666',
                                      '银行存款.一般户.工商银行.工行喜年支行.5922',
                                      '银行存款.一般户.中国电信集团财务有限公司.9601'])]
            dff = df[df['科目描述'].isin(['银行存款.综合户.中信银行.深圳皇岗支行6666',
                                      '银行存款.一般户.工商银行.工行喜年支行.5922',
                                      '银行存款.一般户.中国电信集团财务有限公司.9601'])]
            # 处理空值
            dfs = dfs.copy()
            dff = dff.copy()
            dfs.loc[:, '借方金额'] = 0
            dff.loc[:, '贷方金额'] = 0

            dfa = pd.concat([dfs, dff])
            dfa['差额'] = dfa['借方金额'] - dfa['贷方金额']

            # 生成四个sheet
            dfb = pd.crosstab(dfa['过账日期'], dfa['项目WBS描述'], values=dfa['差额'], aggfunc='sum')
            dfc = dfb.reindex(pd.date_range(start=dfb.index.min(), end=dfb.index.max(), freq='D'), fill_value=0)
            dfd = dfc.cumsum().ffill()
            dfe = dfd.apply(lixi)
            dff = dfe.cumsum()

            # 保存结果文件
            output_path = os.path.join(app.config['UPLOAD_FOLDER'], '深分月度利息计算.xlsx')
            with pd.ExcelWriter(output_path) as writer:
                dfc.T.to_excel(writer, sheet_name='每日差额表')
                dfd.T.to_excel(writer, sheet_name='差额累积和表') 
                dfe.T.to_excel(writer, sheet_name='每日利息表')
                dff.T.to_excel(writer, sheet_name='利息累积和表')
            
            # 准备可视化数据
            dates = dfc.index.strftime('%Y-%m-%d').tolist()
            
            # 获取前五名项目
            top_5_projects = dff.iloc[-1].nlargest(5).index.tolist()
            project_data = {}
            
            for project in top_5_projects:
                project_data[project] = dff[project].fillna(0).tolist()
            
            # 生成表格HTML
            dfc_table = dfc.T.to_html(classes="table table-striped table-bordered", border=0)
            dfd_table = dfd.T.to_html(classes="table table-striped table-bordered", border=0)
            dfe_table = dfe.T.to_html(classes="table table-striped table-bordered", border=0)
            dff_table = dff.T.to_html(classes="table table-striped table-bordered", border=0)
            
            return render_template('result.html',
                                 filename=filename,
                                 output_file=output_path,
                                 dates=json.dumps(dates),
                                 project_data=json.dumps(project_data),
                                 dfc_table=dfc_table,
                                 dfd_table=dfd_table,
                                 dfe_table=dfe_table,
                                 dff_table=dff_table)

        except Exception as e:
            app.logger.error(f"文件处理失败: {str(e)}", exc_info=True)
            flash(f"处理文件时发生错误: {str(e)}", 'danger')
            return render_template('upload.html', form=form)

    return render_template('upload.html', form=form)

@app.route('/download/<path:filename>')
def download(filename):
    return send_file(filename, as_attachment=True)

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000)
